#!/usr/bin/env python
# -*- encoding:    utf-8 -*-
# Author      :    yao low_design@163.com
# Date        :    2025/02/18 15:41:01


import io
import json
import base64
import zipfile
import xlsxwriter
from odoo import models, fields, api, _
from odoo.exceptions import UserError
import logging


_logger = logging.getLogger(__name__)


SUPPORTED_PATHS = (
    "[Content_Types].xml",
    "xl/sharedStrings.xml",
    "xl/styles.xml",
    "xl/workbook.xml",
    "_rels/",
    "xl/_rels",
    "xl/charts/",
    "xl/drawings/",
    "xl/externalLinks/",
    "xl/pivotTables/",
    "xl/tables/",
    "xl/theme/",
    "xl/worksheets/",
)


class SpreadsheetExcel(models.Model):
    _name = "spreadsheet.excel"
    _inherit = ["mail.thread", "mail.activity.mixin", "spreadsheet.mixin"]
    _description = "Spreadsheet Excel"

    # 字段定义
    sequence = fields.Char(
        string="#",
        default=lambda self: self.env["ir.sequence"].next_by_code("ss.excel.sequence"),
        help="电子表格的唯一序列号"
    )
    category = fields.Selection([
        ("price", "价格表"),
        ("product", "产品"),
        ('import_purchase', '导入采购订单'),
        ('import_sale', '导入销售订单'),
    ], string="类别", required=True)
    name = fields.Char(string="名称", required=True)
    file = fields.Binary(string="Excel文件", help="用于将Excel文件转为电子表格", attachment=True)
    file_name = fields.Char(string="文件名称")
    aspreadsheet_id = fields.Many2one("ir.attachment", string="Spreadsheet附件", auto_join=True, copy=True, help="存储转换后的电子表格数据")
    raw = fields.Binary(related="aspreadsheet_id.raw", readonly=False, help="原始电子表格数据")
    mimetype = fields.Char(related="aspreadsheet_id.mimetype", help="文件MIME类型")
    excel_changed = fields.Boolean(default=False, help="标记Excel文件是否被修改")
    content = fields.Binary(string="模板", help="Excel模板文件")
    description = fields.Text(string="描述")
    user_id = fields.Many2one("res.users", string="负责人", help="可以编辑此电子表格的用户")
    company_id = fields.Many2one("res.company", string="公司")
    raw_no_formula = fields.Binary(help="不包含公式的原始数据")

    is_exec = fields.Boolean(string="是否执行", default=False, help="标记电子表格是否已执行")
    is_success = fields.Boolean(string="是否成功", default=False, help="标记电子表格执行是否成功")
    exec_msg = fields.Text(string="执行信息", help="执行电子表格的结果信息")

    # delivery_type = fields.Selection([
    #     ('future', '期货'),
    #     ('spot', '现货')
    # ], string='Delivery Type')

    @api.model_create_multi
    def create(self, vals_list):
        """
        创建的时候要有 sequence

        :param vals: _description_
        """
        for vals in vals_list:
            if not vals.get("sequence"):
                vals["sequence"] = self.env["ir.sequence"].next_by_code("ss.excel.sequence")
        return super(SpreadsheetExcel, self).create(vals_list)

    def _compute_display_name(self):
        """计算显示名称，格式为: 名称: 序号"""
        for rec in self:
            rec.display_name = (
                f"{rec.name}: {rec.sequence}" if rec.sequence else rec.name
            )

    @api.onchange("file")
    def _onchange_file(self):
        """
        文件变更时的处理
        - 如果不是复制操作，则更新名称和变更标志
        """
        if not self._context.get("is_copy"):
            if not self.name:
                self.name = self.file_name
            self.excel_changed = True

    def get_raw_no_formula(self):
        """获取不含公式的原始数据"""
        return base64.b64decode(self.raw_no_formula)

    def sync_excel_to_spreadsheet(self):
        """
        将Excel文件同步为Odoo附件
        1. 解码Excel文件内容
        2. 创建ir.attachment记录
        3. 关联到当前记录
        """
        self.ensure_one()
        file_contents = base64.b64decode(self.file)
        datas = base64.encodebytes(file_contents)
        value = {
            "name": self.name,
            "datas": datas,
            "mimetype": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "public": True,
        }
        self.aspreadsheet_id = self.env["ir.attachment"].create(value).id

    def change_xlsx_into_spreadsheet(self):
        """
        FIXME: excel文件中的图片附件没有转成Odoo附件，因为当时电子表格还不支持插入图像
        FIXME：我们存到raw字段中（binary），documents模块存到了spreadsheet_data中
        """
        self.ensure_one()
        file = io.BytesIO(self.raw)

        _logger.info("Is ZIP file: %s", zipfile.is_zipfile(file))
        _logger.info("Current mimetype: %s", self.mimetype)
        if (
            not zipfile.is_zipfile(file)
            or self.mimetype
            != "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        ):
            raise UserError(_("The file is not a xlsx file"))

        unzipped_size = 0
        with zipfile.ZipFile(file) as input_zip:
            if len(input_zip.infolist()) > 1000:
                raise UserError(_("The xlsx file is too big"))

            if "[Content_Types].xml" not in input_zip.namelist() or not any(
                name.startswith("xl/") for name in input_zip.namelist()
            ):
                raise UserError(_("The xlsx file is corrupted"))

            unzipped = {}
            for info in input_zip.infolist():
                if not info.filename.endswith(
                    (".xml", ".xml.rels")
                ) or not info.filename.startswith(SUPPORTED_PATHS):
                    # Don't extract files others than xmls or unsupported xmls
                    continue

                unzipped_size += info.file_size
                if unzipped_size > 50 * 1000 * 1000:  # 50MB
                    raise UserError(_("The xlsx file is too big"))

                unzipped[info.filename] = input_zip.read(info.filename).decode()

            # self.raw = json.dumps(unzipped)
            self.spreadsheet_data = json.dumps(unzipped)

    def open_spreadsheet(self):
        """
        打开电子表格

        流程:
        1. 检查文件是否变更
        2. 同步Excel文件到附件
        3. 转换为电子表格格式
        4. 返回打开动作
        """
        self.ensure_one()
        if self.excel_changed and self.file:
            self.update_spreadsheet()
        if not self.aspreadsheet_id:
            self.sync_excel_to_spreadsheet()
            self.change_xlsx_into_spreadsheet()
        return {
            "type": "ir.actions.client",
            "tag": "action_edit_fss",
            "params": {
                "spreadsheet_id": self.id,
                'isReadonly': self._check_access_rights(),
            },
        }

    def update_spreadsheet(self):
        """
        更新电子表格

        操作:
        1. 删除旧附件
        2. 清除协作数据
        3. 重置变更标志
        """
        self.ensure_one()
        self.aspreadsheet_id.unlink()
        self.sudo()._delete_collaborative_data()
        self.excel_changed = False

    @api.model
    def excel_contents_to_base64(self, files):
        """
        将Excel内容转换为base64编码

        参数:
        - files: JSON格式的文件列表，包含path和content

        返回:
        - base64编码的Excel文件内容
        """
        files = json.loads(files)
        stream = io.BytesIO()
        with zipfile.ZipFile(stream, "w", compression=zipfile.ZIP_DEFLATED) as doc_zip:
            for f in files:
                doc_zip.writestr(f["path"], f["content"])
        return base64.b64encode(stream.getvalue())

    def _check_access_rights(self, ):
        """
        检测当前用户是否可以编辑此记录
        """
        self.ensure_one()
        if not self.user_id:
            return True
        return self.user_id == self.env.user

    def join_spreadsheet_session(self, access_token=None):
        """
        加入电子表格会话,判断用户是否有权限编辑此记录

        :param access_token: _description_, defaults to None
        """
        res = super().join_spreadsheet_session(access_token)
        can_edit = self._check_access_rights()
        res['isReadonly'] = not can_edit
        return res

    def download_template(self):
        """
        下载附件

        :raises UserError: _description_
        :return: _description_
        """
        self.ensure_one()
        if not self.content:
            raise UserError(_("未配置模板！"))
        action = {
            "type": "ir.actions.act_url",
            "url": "/spreadsheet/excel?spreadsheet_id=%d&time=%s"
            % (self.id, fields.Datetime.now()),
        }
        return action

    def write(self, vals):
        """
        保存电子表格公式计算之后的内容，因为ascii问题而转为base64.
        前端创建记录调用write方法。
        """
        raw_no_formula = False

        if "raw_no_formula" in vals:
            raw_no_formula = vals["raw_no_formula"]
            vals["raw_no_formula"] = base64.b64encode(vals["raw_no_formula"].encode())
        
        # 【优化】增加单次写入长度限制，防止全量json存储导致数据库爆炸
        # 可根据实际业务需要设定限制大小（比如5MB），如需更大请按需调整
        if "raw" in vals or "raw_no_formula" in vals:
            data = vals.get("raw") or vals.get("raw_no_formula")
            # 注意：raw_no_formula此处为base64后的bytes，长度可做判定
            if data and isinstance(data, (str, bytes)) and len(data) > 5 * 1024 * 1024:
                raise UserError(_("单次写入内容过大，请分批处理或用后端导入。"))

        res = super(SpreadsheetExcel, self).write(vals)
        return res
